package git.bankview.sl.report;

import java.util.*;
import java.sql.*;
import java.io.*;

import laya.semi.process.*;

import git.bankview.sl.db.*;
import git.bankview.sl.util.ArrayUtil;
import git.bankview.sl.auth.AuthHelper;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.export.*;
import net.sf.jasperreports.engine.util.*;
import net.sf.jasperreports.engine.fill.*;

import org.apache.log4j.Logger;

public class TableImporter
{
	private static Logger log = Logger.getLogger(TableImporter.class);
	
	public static final String APPEND = "APPEND";
	public static final String UNION = "UNION";
	public static final String UPDATEONLY = "UPDATEONLY";
	public static final String EMPTYSCHDFIRST = "EMPTYSCHDFIRST";
	public static final String EMPTYTABFIRST = "EMPTYTABFIRST";
	
	private boolean m_bNeedAppend = true;
	private boolean m_bUpdateFirst = false;
	private boolean m_bDeleteFirst = false;
	private boolean m_bEmptyFirst = false;
	
	private HashSet m_oDeletedRowSet = null;

	public TableImporter()
	{
	}
	
	public TableImporter(boolean bNeedAppend, boolean bUpdateFirst, boolean bDeleteFirst, boolean bEmptyFirst)
	{
		m_bNeedAppend = bNeedAppend;
		m_bUpdateFirst = bUpdateFirst;
		m_bDeleteFirst = bDeleteFirst;
		m_bEmptyFirst = bEmptyFirst;
	}
	
	public TableImporter(String oLoadPolicy)
		throws IllegalArgumentException
	{
		if (oLoadPolicy.equals(APPEND))
		{
			// use defaults
		}
		else if (oLoadPolicy.equals(UNION))
		{
			m_bUpdateFirst = true;
		}
		else if (oLoadPolicy.equals(UPDATEONLY))
		{
			m_bNeedAppend = false;
			m_bUpdateFirst = true;
		}
		else if (oLoadPolicy.equals(EMPTYSCHDFIRST))
		{
			m_bDeleteFirst = true;
		}
		else if (oLoadPolicy.equals(EMPTYTABFIRST))
		{
			m_bEmptyFirst = true;
		}
		else
		{
			throw new IllegalArgumentException("unexpected policy-string: '" +  oLoadPolicy + "'");
		}
	}
	
	public int dataImport(String tabName, File src, Connection conn)
		throws TableImporterException, IOException
	{
		int nAffectedRows = -1;
		m_oDeletedRowSet = null;
		
		boolean bNeedConvertCharset = !ReportCommon.DB_CHARSETNAME.equals(ReportCommon.DB_CLIENTCHARSETNAME);
		
		BufferedReader reader = 
			new BufferedReader(new InputStreamReader(new FileInputStream(src), ReportCommon.DB_CLIENTCHARSETNAME));
		
		String line = reader.readLine();
		if (null == line)
			return nAffectedRows; // empty file
		
		String[] vFieldExpr = line.split(",");
		if (0 == vFieldExpr.length)
			return nAffectedRows; // zero field file
		
		try
		{
			if (m_bEmptyFirst)
				emptyTable(tabName, conn);
			
			PreparedStatement psDelete = null;
			int[] vDeleteKey = null;
			if (m_bDeleteFirst)
			{
				StringBuffer oSql = new StringBuffer();
				vDeleteKey = buildDeleteSql(oSql, vFieldExpr, tabName);
				psDelete = conn.prepareStatement(oSql.toString());
				m_oDeletedRowSet = new HashSet();
			}
			
			PreparedStatement psAppend = null;
			if (m_bNeedAppend)
			{
				StringBuffer oSql = new StringBuffer();
				buildAppendSql(oSql, vFieldExpr, tabName);
				psAppend = conn.prepareStatement(oSql.toString());
			}
			
			PreparedStatement psUpdate = null;
			int[] vUpdateFieldQ = null;
			if (m_bUpdateFirst)
			{
				StringBuffer oSql = new StringBuffer();
				vUpdateFieldQ = buildUpdateSql(oSql, vFieldExpr, tabName);
				psUpdate = conn.prepareStatement(oSql.toString());
			}
			
			int d = 0;
			nAffectedRows = 0;
			int ln = 0;
			while(null != (line = reader.readLine()))
			{
				++ln;
				if (bNeedConvertCharset)
					line = new String(line.getBytes(ReportCommon.DB_CLIENTCHARSETNAME), ReportCommon.DB_CHARSETNAME);
					
				String[] v = git.bankview.sl.util.StringUtil.split(line, ","); //line.split(",");
				//log.debug(ArrayUtil.toString(v));
				if (vFieldExpr.length > v.length)
				{
					throw new ArrayIndexOutOfBoundsException("field number not match (Ln: " + ln + ")");
				}
				
				if (m_bDeleteFirst)
				{
					if (haveBeenDeleted(vDeleteKey, v))
					{
						for (int i = 0; i < vDeleteKey.length; ++i)
						{
							psDelete.setString(i + 1, v[vDeleteKey[i]]);
						}
						
						psDelete.executeUpdate();
					}
				}
				
				boolean bUpdate = false;
				
				if (m_bUpdateFirst)
				{
					//log.debug("UPDATE order: " + ArrayUtil.toString(vUpdateFieldQ));
					for (int i = 0; i < vFieldExpr.length; ++i)
					{
						psUpdate.setString(i + 1, v[vUpdateFieldQ[i]]);
					}
					
					d = psUpdate.executeUpdate();
					bUpdate = (0 < d);
					nAffectedRows += d;
				}
				
				if (m_bNeedAppend && !bUpdate)
				{
					for (int i = 0; i < vFieldExpr.length; ++i)
					{
						psAppend.setString(i + 1, v[i]);
					}
					
					d = psAppend.executeUpdate();
					nAffectedRows += d;
				}
			}
			
			return nAffectedRows;
		}
		catch(Exception e)
		{
			log.warn("ERROR OCCURED at: [" + line + "]", e);
			throw new TableImporterException(line, e);
		}
	}
	
	private boolean haveBeenDeleted(int[] vDeleteKey, String[] vFieldValue)
	{
		Vector row = new Vector();
		for (int i = 0; i < vDeleteKey.length; ++i)
			row.addElement(vFieldValue[vDeleteKey[i]]);
		
		return !m_oDeletedRowSet.add(row);
	}
	
	private void emptyTable(String tabName, Connection conn)
		throws SQLException
	{
		log.warn("START EMPTY TABLE: '" + tabName + "'");
		Statement st = conn.createStatement();
		int nRow = st.executeUpdate("delete from " + tabName);
		log.warn("DELETE " + nRow + " ROW(S) FROM '" + tabName + "'");
	}

	private int[] buildDeleteSql(StringBuffer oSql, String[] vFieldExpr, String tabName)
	{
		Vector oKeyList = new Vector();
		
		oSql.append("delete from ").append(tabName).append(" where ");
		for (int i = 0; i < vFieldExpr.length; ++i)
		{
			if ('*' == vFieldExpr[i].charAt(0))
			{
				if (0 < oKeyList.size())
					oSql.append(" and ");
				oSql.append(vFieldExpr[i].substring(1)).append(" = ?");
				
				oKeyList.addElement(new Integer(i));
			}
		}
		
		int[] vKey = new int[oKeyList.size()];
		for (int i = 0; i < vKey.length; ++i)
		{
			vKey[i] = ((Integer)oKeyList.get(i)).intValue();
		}
		
		log.debug(oSql);
		return vKey;
	}

	private void buildAppendSql(StringBuffer oSql, String[] vFieldExpr, String tabName)
	{
		oSql.append("insert into ").append(tabName).append(" (");
		for (int i = 0; i < vFieldExpr.length; ++i)
		{
			String fieldName = 
				('*' == vFieldExpr[i].charAt(0) ? vFieldExpr[i].substring(1) : vFieldExpr[i]);
			
			if (0 < i)
				oSql.append(",");
			
			oSql.append(fieldName);	
		}
		oSql.append(") values (");
		for (int i = 0; i < vFieldExpr.length; ++i)
		{
			if (0 < i)
				oSql.append(",");
			
			oSql.append("?");	
		}
		oSql.append(")");
		
		log.debug(oSql);
	}
	
	private int[] buildUpdateSql(StringBuffer oSql, String[] vFieldExpr, String tabName)
	{
		int[] vUpdateFieldQ = new int[vFieldExpr.length];
		int idxHead = -1;
		
		oSql.append("update ").append(tabName).append(" set ");
		for (int i = 0; i < vFieldExpr.length; ++i)
		{
			if ('*' == vFieldExpr[i].charAt(0))
			{
				// pk
				vUpdateFieldQ[vUpdateFieldQ.length + idxHead - i] = i;
			}
			else
			{
				// non-pk
				vUpdateFieldQ[++idxHead] = i;
				if (0 < idxHead)
					oSql.append(", ");
				
				oSql.append(vFieldExpr[i]).append(" = ?");
			}
		}
		oSql.append(" where ");
		for (int i = idxHead + 1; i < vFieldExpr.length; ++i)
		{
			if (idxHead + 1 < i)
				oSql.append(" and ");
			
			oSql.append(vFieldExpr[vUpdateFieldQ[i]].substring(1)).append(" = ?");
		}
		
		log.debug(oSql);
		return vUpdateFieldQ;
	}
}
